{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Prob 1","Scenario Summary","Prob 2","Prob 3","Prob 4","Prob 5"],"not_rendered":{"charts":2,"images":0,"macros":1,"external_links":0,"defined_names":5,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Prob 1","index":0,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Annual Investment",5500]
["Number of Years",35]
["Payments per Year",1]
[]
["Fund","Rate of Return","Terminal Value"]
["Money Market",0.01,{"$f":"FV(B6/$B$3,$B$2*$B$3,-$B$1/$B$3)","v":229131.51581719751}]
["Long-Term Bond",4.4999999999999998E-2,{"$f":"FV(B7/$B$3,$B$2*$B$3,-$B$1/$B$3)","v":448231.39900299732}]
["Conservative Stock",6.5000000000000002E-2,{"$f":"FV(B8/$B$3,$B$2*$B$3,-$B$1/$B$3)","v":682190.79643252236}]
["Aggressive Stock",0.11,{"$f":"FV(B9/$B$3,$B$2*$B$3,-$B$1/$B$3)","v":1878742.5513733595}]

{"sheet":"Scenario Summary","index":1,"n_rows":14,"n_cols":8,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Scenario Summary"]
[null,null,null,"Annual Investment","Quarterly Investment","Monthly Investment","Bi-Weekly Investment","Weekly Investment"]
[null,null,null,"Created by Timothy R. Mayes, Ph.D. on  3/16/2014","Created by Timothy R. Mayes, Ph.D. on 3/16/2014","Created by Timothy R. Mayes, Ph.D. on 3/16/2014","Created by Timothy R. Mayes, Ph.D. on 3/16/2014","Created by Timothy R. Mayes, Ph.D. on 3/16/2014"]
[null,"Changing Cells:"]
[null,null,"Payments_per_Year",1,4,12,26,52]
[null,"Result Cells:"]
[null,null,"Money_Market",229131.515817198,230146.33107359,230373.40216754799,230434.63417033901,230460.88914057199]
[null,null,"Long_term_Bond",448231.39900299697,463032.62937889301,466465.06348238798,467398.49104750901,467799.75396321801]
[null,null,"Conservative_Stock",682190.79643252201,723600.78913511895,733478.25401349296,736182.65172370104,737347.63859136798]
[null,null,"Aggessive_Stock",1878742.55137336,2180623.5401436202,2258802.5021606302,2280648.1245539002,2290117.97482923]
[null,"Notes:  Current Values column represents values of changing cells at"]
[null,"time Scenario Summary Report was created.  Changing cells for each"]
[null,"scenario are highlighted in gray."]

{"sheet":"Prob 2","index":2,"n_rows":21,"n_cols":14,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Parts A & B"]
[null,"Req. Ret.",7.0000000000000007E-2]
[]
[null,"Period","Cash Flow","Cash Flow"]
[null,1,45000,{"$f":"C9","v":25000},null,null,null,null,null,null,null,null,"Change in Cash Flow",-5000]
[null,2,{"$f":"C5+$N$5","v":40000},{"$f":"C8","v":30000}]
[null,3,{"$f":"C6+$N$5","v":35000},{"$f":"C7","v":35000}]
[null,4,{"$f":"C7+$N$5","v":30000},{"$f":"C6","v":40000}]
[null,5,{"$f":"C8+$N$5","v":25000},{"$f":"C5","v":45000}]
[null,"PV",{"$f":"NPV($C$2,C5:C9)","v":146275.56043698086},{"$f":"NPV($C$2,D5:D9)","v":140738.26007935067}]
[null,"FV",{"$f":"FV($C$2,COUNT(C5:C9),,-C10)","v":205159.04044999997},{"$f":"FV($C$2,COUNT(D5:D9),,-D10)","v":197392.69025000001}]
[]
["Part C"]
[null,"Period","Cash Flow","Cash Flow"]
[null,0,-130000,{"$f":"C15","v":-130000}]
[null,1,{"$f":"C5","v":45000},{"$f":"D5","v":25000}]
[null,2,{"$f":"C6","v":40000},30000]
[null,3,35000,35000]
[null,4,30000,40000]
[null,5,25000,45000]
[null,"IRR",{"$f":"IRR(C15:C20)","v":0.12079113169417854},{"$f":"IRR(D15:D20)","v":9.7481287919436888E-2}]

{"sheet":"Prob 3","index":3,"n_rows":42,"n_cols":11,"rows_kept":42,"cut":false,"hidden":false,"merged":[]}
["Cost of College Today",18391,null,null,null,"Time Line When Cash Flows Start in Period 1"]
["Annual Rate of Increase",3.2000000000000001E-2,null,null,null,"Period","Cash Flow","Cash Flow","Cash Flow","Cash Flow","Cash Flow"]
["Annual Cost of College",{"$f":"B1*(1+B2)^B5","v":27697.553452791421},null,null,null,1,0,{"$f":"$B$12","v":5055.8240240467785},{"$f":"$B$13","v":3728.8738548020833},{"$f":"$B$14","v":4304.5413218857875},{"$f":"$B$15","v":3174.772602003562}]
["Annual Rate of Return",0.08,null,null,null,2,0,5055.8240240467785,{"$f":"$B$13","v":3728.8738548020833},4304.5413218857875,3174.772602003562]
["Years until college",13,null,null,null,3,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Years in College",4,null,null,null,4,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Inheritance",10000,null,null,null,5,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
[null,null,null,null,null,6,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Assuming First Cash Flow in Period 1",null,null,null,null,7,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Amount needed in 12 years",{"$f":"-PV((1+B4)/(1+B2)-1,B6,B3)/(1+B2)","v":95945.011865041219},null,null,null,8,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Lump sum required today",{"$f":"PV(B4,B5-1,,-B10)","v":38101.084285060744},null,null,null,9,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Annual investment required (ending in year 12)",{"$f":"PMT(B4,B5-1,,-B10)","v":5055.8240240467785},null,null,null,10,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Annual investment with inheritance (ending in year 12)",{"$f":"PMT(B4,B5-1,B7,-B10)","v":3728.8738548020833},null,null,null,11,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Annual investment required (ending in year 16)",{"$f":"PMT(B4,16,-B11)","v":4304.5413218857875},null,null,null,12,0,5055.8240240467785,3728.8738548020833,4304.5413218857875,3174.772602003562]
["Annual investment with inheritance (ending in year 16)",{"$f":"PMT(B4,B5+B6-1,-(B11-B7))","v":3174.772602003562},null,null,null,13,{"$f":"$B$3","v":27697.553452791421},0,0,4304.5413218857875,3174.772602003562]
[null,null,null,null,null,14,{"$f":"G15*(1+$B$2)","v":28583.875163280747},0,0,4304.5413218857875,3174.772602003562]
[null,null,null,null,null,15,{"$f":"G16*(1+$B$2)","v":29498.559168505733},0,0,4304.5413218857875,3174.772602003562]
[null,null,null,null,null,16,30442.513061897916,0,0,4304.5413218857875,3174.772602003562]
[null,null,null,null,null,"PV",{"$f":"NPV($B$4,G3:G18)","v":38101.08428506073},{"$f":"NPV($B$4,H3:H18)","v":38101.084285060744},{"$f":"NPV($B$4,I3:I18)+B7","v":38101.084285060744},{"$f":"NPV($B$4,J3:J18)","v":38101.084285060737},{"$f":"NPV($B$4,K3:K18)+B7","v":38101.084285060737}]
[]
[]
[]
[null,null,null,null,null,"Time Line When Cash Flows Start in Period 0"]
["Assuming First Cash Flow in Period 0",null,null,null,null,"Period","Cash Flow","Cash Flow","Cash Flow","Cash Flow","Cash Flow"]
["Amount needed in 13 years",{"$f":"-PV((1+B4)/(1+B2)-1,B6,B3,0,1)","v":103620.61281424452},null,null,null,0,0,{"$f":"$B$27","v":4463.5351515666734},{"$f":"$B$28","v":3292.0369553022706},{"$f":"$B$29","v":3985.6864091535076},{"$f":"$B$30","v":2939.6042611144094}]
["Lump sum required today",{"$f":"PV(B4,B5,,-B25)","v":38101.084285060751},null,null,null,1,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
["Annual investment required (ending in year 12)",{"$f":"PMT(B4,B5,,-B25,1)","v":4463.5351515666734},null,null,null,2,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
["Annual investment with inheritance (ending in year 12)",{"$f":"PMT(B4,B5,B7,-B25,1)","v":3292.0369553022706},null,null,null,3,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
["Annual investment required (ending in year 15)",{"$f":"PMT(B4,B5+B6-1,-B26,,1)","v":3985.6864091535076},null,null,null,4,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
["Annual investment with inheritance (ending in year 16)",{"$f":"PMT(B4,B5+B6-1,-(B26-B7),,1)","v":2939.6042611144094},null,null,null,5,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,6,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,7,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,8,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,9,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,10,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,11,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,12,0,4463.5351515666734,3292.0369553022706,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,13,{"$f":"$B$3","v":27697.553452791421},0,0,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,14,{"$f":"G38*(1+$B$2)","v":28583.875163280747},0,0,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,15,{"$f":"G39*(1+$B$2)","v":29498.559168505733},0,0,3985.6864091535076,2939.6042611144094]
[null,null,null,null,null,16,30442.513061897916,0,0,0,0]
[null,null,null,null,null,"PV",{"$f":"NPV($B$4,G25:G41)*(1+B4)","v":38101.08428506073},{"$f":"NPV($B$4,H25:H41)*(1+B4)","v":38101.084285060744},{"$f":"NPV($B$4,I25:I41)*(1+B4)+B7","v":38101.084285060759},{"$f":"NPV($B$4,J25:J41)*(1+B4)","v":38101.084285060744},{"$f":"NPV($B$4,K25:K40)*(1+B4)+B7","v":38101.084285060737}]

{"sheet":"Prob 4","index":4,"n_rows":31,"n_cols":3,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["Growth Rate",0.05]
["Required Return",0.11]
["Year 1 Rent",20000]
["Years Stable Rent",3]
["Years Growing Rent",7]
["Year Sold",10]
["Sale Price in 10 Years",300000]
["Ask Price Today",200000]
[]
["A.","Period","Cash Flow"]
[null,0,{"$f":"-B8","v":-200000}]
[null,1,{"$f":"$B$3","v":20000}]
[null,2,{"$f":"$B$3","v":20000}]
[null,3,20000]
[null,4,{"$f":"C14*(1+$B$1)","v":21000}]
[null,5,22050]
[null,6,23152.5]
[null,7,24310.125]
[null,8,25525.631250000002]
[null,9,26801.912812500002]
[null,10,{"$f":"C20*(1+$B$1)+B7","v":328142.00845312502}]
[]
["B.","Present Value",{"$f":"NPV(B2,C12:C21)","v":237000.99626880742}]
["C.","Present Value",{"$f":"-PV(B2,B4,B3)-PV((1+B2)/(1+B1)-1,B5,B3*(1+B1))/(1+B1)/(1+B2)^B4-PV(B2,B6,0,B7)","v":237000.99626880744}]
[]
[null,"PV First 3 Years",{"$f":"PV(B2,B4,-C12)","v":48874.294308918143}]
[null,"PV Next 7 Years",{"$f":"(-PV((1+B2)/(1+B1)-1,B5,C15)/(1+B1))/(1+B2)^B4","v":82471.358327549227}]
[null,"PV Lump Sum",{"$f":"-PV(B2,B6,0,B7)","v":105655.34363234007}]
[null,"Total PV",{"$f":"SUM(C26:C28)","v":237000.99626880744}]
[]
["D.","Yield",{"$f":"IRR(C11:C21)","v":0.13692232601799814}]

{"sheet":"Prob 5","index":5,"n_rows":38,"n_cols":8,"rows_kept":38,"cut":false,"hidden":false,"merged":[]}
["Advertised Prize",105000000]
["Number of Payments",30,null,null,null,null,"Graduated Annuity Table"]
["Interest Rate",4.4999999999999998E-2,null,null,null,null,"Disc Rate",{"$f":"B3","v":4.4999999999999998E-2}]
[null,null,null,null,null,null,"Growth",0.03]
["As an Annuity Due",null,null,null,null,null,"Period","Cash Flow"]
["Annual Payment",{"$f":"B1/B2","v":3500000},null,null,null,null,0,2207022.2286265213]
["Cash Prize",{"$f":"PV(B3,B2,-B6,0,1)","v":59576609.850736365},null,null,null,null,1,{"$f":"H6*(1+$H$4)","v":2273232.8954853169}]
[null,null,null,null,null,null,2,{"$f":"H7*(1+$H$4)","v":2341429.8823498762}]
["As a Graduated Annuity Due",null,null,null,null,null,3,2411672.7788203727]
["First Payment",{"$f":"H6","v":2207022.2286265213},null,null,null,null,4,2484022.9621849838]
["Cash Prize",{"$f":"-PV((1+H3)/(1+H4)-1,B2,B10,0,1)","v":54109905.665661544},54109905.665660918,null,null,null,5,2558543.6510505332]
[null,null,null,null,null,null,6,2635299.9605820491]
[null,null,null,null,null,null,7,2714358.9593995106]
[null,null,null,null,null,null,8,2795789.7281814963]
[null,null,null,null,null,null,9,2879663.4200269412]
[null,null,null,null,null,null,10,2966053.3226277498]
[null,null,null,null,null,null,11,3055034.9223065823]
[null,null,null,null,null,null,12,3146685.9699757798]
[null,null,null,null,null,null,13,3241086.5490750531]
[null,null,null,null,null,null,14,3338319.1455473048]
[null,null,null,null,null,null,15,3438468.7199137239]
[null,null,null,null,null,null,16,3541622.7815111359]
[null,null,null,null,null,null,17,3647871.4649564698]
[null,null,null,null,null,null,18,3757307.6089051641]
[null,null,null,null,null,null,19,3870026.8371723192]
[null,null,null,null,null,null,20,3986127.642287489]
[null,null,null,null,null,null,21,4105711.471556114]
[null,null,null,null,null,null,22,4228882.8157027978]
[null,null,null,null,null,null,23,4355749.3001738815]
[null,null,null,null,null,null,24,4486421.7791790981]
[null,null,null,null,null,null,25,4621014.4325544713]
[null,null,null,null,null,null,26,4759644.8655311055]
[null,null,null,null,null,null,27,4902434.2114970386]
[null,null,null,null,null,null,28,5049507.2378419498]
[null,null,null,null,null,null,29,5200992.4549772087]
[]
[null,null,null,null,null,null,"PV",{"$f":"NPV(H3,H6:H35)*(1+H3)","v":54109905.665661395}]
[null,null,null,null,null,null,"Sum of CF",{"$f":"SUM(H6:H35)","v":105000000.00000003}]